package com.navi.loaddata.loadService.impl.spcData;
import com.navi.loaddata.core.mysqlEntity.SpcEtlErrorData;
import com.navi.loaddata.core.repository.impl.MesRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.oracleEntity.spc.*;
import com.navi.loaddata.core.util.JdbcUtil2;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.text.SimpleDateFormat;
import java.util.*;



/*@Transactional(transactionManager = "mesPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})*/
@Service
@Transactional
public class LoadSpcDataService2 {
    private static final Logger logger = LoggerFactory.getLogger(LoadSpcDataService2.class);
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");//yyyy-mm-dd hh:mm:ss   yyyyMMddHHmmss
    public static final String CONTROLDATA_QUERY_COLUMN =" CHARTID ,CONTROLCHARTTYPE ,CHARTOWNER ,FACTORYNAME ,SUBFACTORYNAME ," +
            "     CONTROLDATASEQ ,DCDATAID ,TIMEKEY ,DCTYPE ,CHARTTYPE_EXT ," +
            "     MATERIALNAME ,ALIASITEMNAME ,PRODUCTSPECNAME ,ECCODE ,RUNMODE ,OWNERCODE ,RTC ,QTAP ,EventUser," +
            "     EVENTTIME ,PROCESSOPERATIONNAME ,MACHINENAME ,UNITNAME ,RECIPENAME ," +
            "     EVENTTIME_PRE ,PROCPROCESSOPERATIONNAME ,PROCMACHINENAME ,PROCUNITNAME ,PROCRECIPENAME ," +
            "     USPEC ,TARGET ,LSPEC ,UCL1 ,CL1 ,LCL1 ,UCL2 ,CL2 ,LCL2 ,UCL3 ,CL3 ,LCL3 ," +
            "     D_XBAR ,D_SDEV ,D_RNG ,D_UNF1 ,D_UNF2 ,D_MAX ,D_MIN ,D_X ,D_RM ,D_NP ,D_P ,D_C ,D_U ,DEL_MARK ," +
            "     OOS ,OOC1 ,OOC2 ,OOC3 ,OOR1 ,OOR2 ,OOR3 ,SITECOUNT ,RAWDATAS";

    @Autowired
    private MesRepositoryImpl mes;
    QueryRunner qr_mes = JdbcUtil2.getMesProdRunner();
    @Autowired
    private MySqlRepositoryImpl dao;


    public int loadData(EtlConfD etlConfD) throws Exception {
        List<SpcEtlErrorData> spcEtlErrList= new ArrayList<SpcEtlErrorData>();
        List<SpcEtlErrorData> spcEtlErrListToMySql = new ArrayList<SpcEtlErrorData>();
        Session session = mes.getEntityManagerSession();
        logger.info("{}loadData 开始执行 ！start:{},end:{}",etlConfD.getJobName(),sdf.format(etlConfD.getRunStartTimestamp()),sdf.format(etlConfD.getRunEndTimestamp()));
        int count =0;
        String tableName = "";
        String name = "SPC_CHART_";
        String type = etlConfD.getCellType().equals("Inspection") ? "M_" : "P_";
        String shop = etlConfD.getShop() + "_";
        String eqpt = "";
        String chartId="";
        try {
            List<ChartType> chartTypeList = getChartTypeByChartId( etlConfD);
            if(chartTypeList.size()<=0){
                logger.info("{} chartTypeList.size()<=0,loadData 执行结束!",etlConfD.getJobName());
                return count;
            }
            logger.info("****** chartTypeList.size()={}",chartTypeList.size());
            List<String> tableNameList =getControlDataTableNames();
            List<ChartData> chartDataList =new ArrayList<>();
            long startTime=System.currentTimeMillis();
            for(ChartType chartType:chartTypeList){
                chartId=chartType.getChartId();
                if(!StringUtils.isNotBlank(chartType.getProcMachineName())){
                    eqpt ="OTHER";
                }else{
                    if (etlConfD.getCellType().equals("Inspection")) {
                        eqpt = chartType.getProcMachineName().substring(0, 4);
                    } else if (etlConfD.getCellType().equals("Production")) {
                        eqpt = chartType.getProcMachineName().substring(0, 6);
                    }
                }
                tableName = name + type + shop + eqpt;
                if(tableNameList.contains(tableName)){

                }else{
                    tableName = name + type + shop +"OTHER";
                }
                count+= saveChartDataByChartId( etlConfD,chartType.getChartId(),tableName,spcEtlErrList,session);
                if(chartDataList.size()<=0) continue;
            }
            long endTime=System.currentTimeMillis();
            Map<String,String> resultMap= new HashMap<>(3);
            for(int i=0;i<spcEtlErrList.size();i++){
                for(int j=0;j<2;j++){
                    resultMap=reSaveChartDataByChartId(spcEtlErrList.get(i),session);
                    if("success".equals(resultMap.get("flag"))){
                        break;
                    }
                    if(j==1 && "error".equals(resultMap.get("flag"))){
                        SpcEtlErrorData spcEtlErrorData =new SpcEtlErrorData();
                        spcEtlErrorData.setChartId(spcEtlErrList.get(i).getChartId());
                        spcEtlErrorData.setTabName(spcEtlErrList.get(i).getTabName());
                        spcEtlErrorData.setStartSeq(spcEtlErrList.get(i).getStartSeq());
                        spcEtlErrorData.setEndSeq(spcEtlErrList.get(i).getEndSeq());
                        spcEtlErrorData.setErrorMessage(resultMap.get("message"));
                        spcEtlErrListToMySql.add(spcEtlErrorData);
                    }
                }

            }
            updateSpcControlChart(etlConfD,session);
            if(spcEtlErrListToMySql.size()>0){
                saveSpcEtlErrorData(spcEtlErrListToMySql);
            }

            logger.info("{} loadData 执行结束 {}ms！,count:{}",etlConfD.getJobName(),endTime-startTime,count);

        }catch(SQLIntegrityConstraintViolationException e){
            logger.error("跳过主键重复数据");
        } catch (Exception e) {
            logger.error(" {} #################################################：tableName:{}--chartId:{}--start:{}--end:{},error{}",etlConfD.getJobName(),tableName,chartId,sdf.format(etlConfD.getRunStartTimestamp()),sdf.format(etlConfD.getRunEndTimestamp()),LoggerUtil.stackTraceToString(e));
            throw new Exception(e.getMessage());
        }
        return  count;
    }
    public List<ChartType> getChartTypeByChartId(EtlConfD etlConfD)throws Exception{
        List<ChartType> list =new ArrayList<>();
        String shopId =etlConfD.getShop();
        int type   = Integer.parseInt(etlConfD.getExt1());
        String eqpt   =etlConfD.getEqpt();
        String dcType =etlConfD.getCellType();
        String sql="";
        try{
            sql=" SELECT CD.ChartID as CHARTID,                                        "+
                    "            CTF.Factoryname as FACTORYNAME,                               "+
                    "            CTF.dctype as DCType,                                    "+
                    "            CTF.Machinename as PROCMACHINENAME,                               "+
                    "            CC.CONTROLCHARTTYPE as CONTROLCHARTTYPE,                           "+
                    "            EV.UDF1 AS ITEM1,                              "+
                    "            EV.UDF2 AS ITEM2,                              "+
                    "            EV.UDF3 AS ITEM3                               "+
                    "       FROM (SELECT /*+ INDEX(CD SPC_CONTROLDATA_IDX1) */  "+
                    "                    DISTINCT CD.ChartID                    "+
                    "               FROM spc_controldata CD    WHERE   1=1         "+
//                     "and CD.chartid='495A3ANK1_A5870_WETX05_CD1_LOSS'"+
                    //                   "         AND   CD.TIMEKEY >= '201806231400'                    "+
                    //                   "                    AND CD.TIMEKEY < '201806231630'        "+
                    " AND CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())+"'" +
                    " AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' "+
                    "            ) CD,                                          "+
                    "            spc_chartdef CTF,                              "+
                    "            spc_CONTROLCHART CC,                           "+
                    "            SPC_ENUMDEFVALUE EV   WHERE                         "+
                    "           CTF.FactoryName = '"+shopId+"'                    "+
                    "            AND CTF.DCType = '"+dcType+"'                  "+
//                     "           CTF.FactoryName = 'ACF'                    "+
                    //                    "            AND CTF.DCType = 'Inspection'                  "+
                    "            AND CTF.chartid = CD.chartid                   "+
                    "            AND CC.ChartID = CD.ChartID                    "+
                    "            AND EV.ACTIVEFLAG = 'Y'                        "+
                    "            AND EV.enumname = 'ControlChartType'           "+
                    "            AND EV.ENUMVALUE = CC.CONTROLCHARTTYPE         ";
            if(StringUtils.isNotBlank(eqpt) && type==0){
                sql+=" AND CTF.Machinename in( "+eqpt+") ";
            }
            if(StringUtils.isNotBlank(eqpt) && type==1){
                sql+=" AND  regexp_like(CTF.Machinename ,'^"+eqpt+"') ";
            }
            sql+=    "   ORDER BY machinename, chartid                           ";
            list=qr_mes.query(sql,new BeanListHandler<ChartType>(ChartType.class));
            return list;
        }catch (Exception e){
            logger.error("获取 chartType shopId:{}---dcType:{}---start:{}---end:{}",shopId,dcType,sdf.format(etlConfD.getRunStartTimestamp()),sdf.format(etlConfD.getRunEndTimestamp()));
            throw new Exception("获取 chartType Exception:"+ e.getMessage());
        }

    }
    public List<String> getControlDataTableNames()throws Exception{
        List<String> list=new ArrayList<>();
        try{
            list =mes.findBySQL(" select t.table_name from user_tables t where t.table_name like 'SPC_CHART_%' ");
        }catch (Exception e){
            logger.error(" 获取  SPC tableName error {}",e);
            throw new Exception("获取  SPC tableName  Exception:"+ e);
        }
        return list;
    }
    public int saveChartDataByChartId(EtlConfD etlConfD, String chartId,String tableName,List<SpcEtlErrorData> spcEtlErrList,Session session ) throws Exception{
        int count =0;
        StringBuffer sqlbuff = new StringBuffer();
        try {
            String sql  =" select CHOT_SPC_DATA_ALL.CHARTID, CONTROLCHARTTYPE ,CHARTOWNER, FactoryName, SubFactoryName,       "+
                    "        CHOT_SPC_DATA_ALL.CONTROLDATASEQ,DCDATAID, Timekey,DCType,ChartType,                             "+
                    "        MATERIALNAME,ALIASITEMNAME ,PRODUCTSPECNAME,ECCODE,RUNMODE,OWNERCODE,RTC,QTap,EventUser,         "+
                    "        to_date(EVENTTIME,'YYYY-MM-DD HH24:MI:SS'),PROCESSOPERATIONNAME,MACHINENAME,UNITNAME,RECIPENAME, "+
                    "        ProcProcessTime,PROCPROCESSOPERATIONNAME,PROCMACHINENAME,PROCUNITNAME,PROCRECIPENAME,            "+
                    "        USPEC,TARGET,LSPEC,UCL1,CL1,LCL1,UCL2,CL2,LCL2,UCL3,CL3,LCL3,                                    "+
                    "        XBAR,S,R,U1,U2,MAX,MIN,I,MR,NP,P,C,U,MARKINGFLAG,                                                "+
                    "        case when OOSALARMID>0 then 'Y' else 'N' end as OOS,                                             "+
                    "        case when OOCR_ALARMID> 0 or OOC1>0 then 'Y'  else 'N'  end as OOC1,                             "+
                    "        case when OOC2>0 then 'Y' else 'N' end as OOC2,                                                  "+
                    "        case when OOC3>0 then 'Y' else 'N' end as OOC3,                                                  "+
                    "        OOR1,OOR2,OOR3,                                                                                  "+
                    "        RawDataQty,  RAWDATA                                                                             "+
                    " from                                                                                                                                        "+
                    "    (                                                                                                                                        "+
                    "     SELECT SPC_ALLDATA.*, case when SPC_ALLDATA.rawdata is  null then 0 else REGEXP_COUNT(SPC_ALLDATA.rawdata,',')+1 end as RawDataQty      "+
                    "     FROM (                                                                                                                                  "+
                    "         SELECT SPC_DERIVEDDATA.CHARTID,SPC_DERIVEDDATA.CONTROLDATASEQ,CNCT.CONTROLCHARTTYPE , CNCT.CHARTOWNER,SPC_DERIVEDDATA.ChartType,    "+
                    "               DD.DCDATAID,DD.MATERIALNAME,                                                                                                  "+
                    "               TO_CHAR (DD.PROCESSTIME, 'YYYY-MM-DD HH24:MI:SS')  AS PROCESSTIME,SPC_DERIVEDDATA.Timekey,                                    "+
                    "               TO_CHAR (DD.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS')  AS EVENTTIME,                                                                "+
                    "               CASE  WHEN MD.MARKINGTIME IS NOT NULL THEN 'Y'  ELSE 'N' END AS MARKINGFLAG,                                                  "+
                    "               DD.PRODUCTSPECNAME,DD.ECCODE,DD.OWNERCODE, DD.QTap,DD.RTC,DD.RUNMODE,                                                         "+
                    "               DD.PROCESSOPERATIONNAME,DD.MACHINENAME,DD.UNITNAME,DD.SUBUNITNAME,DD.RECIPENAME,DD.EventUser,                                 "+
                    "               CTDF.ALIASITEMNAME ,CTDF.DCType, CTDF.FactoryName, CTDF.SubFactoryName,                                                       "+
                    "               SPC_DERIVEDDATA.XBAR,SPC_DERIVEDDATA.S,SPC_DERIVEDDATA.R,SPC_DERIVEDDATA.U1,SPC_DERIVEDDATA.U2,SPC_DERIVEDDATA.MIN,           "+
                    " 			  SPC_DERIVEDDATA.MAX,                                                                                                            "+
                    "               SPC_DERIVEDDATA.I,SPC_DERIVEDDATA.MR,SPC_DERIVEDDATA.NP,SPC_DERIVEDDATA.P,SPC_DERIVEDDATA.C,SPC_DERIVEDDATA.U                 "+
                    "               ,DR.ProcProcessTime                                                                                                           "+
                    "               ,DR.PROCPROCESSOPERATIONNAME,DR.PROCRECIPENAME,DR.PROCMACHINENAME,DR.PROCUNITNAME,DR.PROCSUBUNITNAME                          "+
                    "               ,MAX (SPC_DERIVEDDATA.ALARMTYPE) AS ALARMTYPE                                                                                 "+
                    "               ,  LISTAGG (CDR.RESULT, ',')  WITHIN GROUP (ORDER BY CDR.SITENAME ) AS RAWDATA                                                "+
                    "         FROM (                                                                                                                              "+
                    "             SELECT *                                                                                                                        "+
                    "             FROM (  SELECT CD.CHARTID,  CD.CONTROLDATASEQ, CD.DCDATAID,CD.Timekey,   CDS.CHARTNAME,EDV_1.ChartType,                         "+
                    "                            NVL (CDR.RESULT, '-123456.789') AS RESULT,                                                                       "+
                    "                            MAX (  MAX (AR.ALARMTYPE)) OVER (  PARTITION BY CD.CHARTID, CD.CONTROLDATASEQ, CD.DCDATAID)  AS ALARMTYPE        "+
                    "                     FROM (SELECT /*+ INDEX(CD SPC_CONTROLDATA_PK) */                                                                        "+
                    "                                  CD.CHARTID,  CD.CONTROLDATASEQ,  CD.DCDATAID,CD.Timekey                                                    "+
                    "                             FROM SPC_CONTROLDATA CD   WHERE                                                                                       "+
//                   "                                CD.chartid='495A3ANK1_A5870_WETX05_CD1_LOSS'                                                           "+
//                    "                                   AND CD.ControlDataSeq >= '201806231400' AND CD.ControlDataSeq < '201806231600'                            "+
                    "  CD.chartid='"+chartId+"' "+
                    "   and CD.ControlDataSeq >= '"+sdf.format(etlConfD.getRunStartTimestamp())+"'" +
                    "   AND  CD.ControlDataSeq< '"+sdf.format(etlConfD.getRunEndTimestamp())+"' "+
                    "                           ) CD                                                                                                              "+
                    "                           INNER JOIN SPC_CONTROLDATASPEC CDS                                                                                "+
                    "                              ON     CDS.CHARTID = CD.CHARTID AND CDS.CONTROLDATASEQ =  CD.CONTROLDATASEQ                                    "+
                    "                           INNER JOIN (select ENUMVALUE, (UDF1 || ',' || UDF2 || ',' || UDF3) as ChartType  from SPC_ENUMDEFVALUE            "+
                    " 						  where ENUMNAME =  'ControlChartType' and UDF1 is not NULL )  EDV_1                                                  "+
                    "                              ON     EDV_1.ENUMVALUE =   CDS.CONTROLCHARTTYPE                                                                "+
                    "                           INNER JOIN SPC_ENUMDEFVALUE EDV                                                                                   "+
                    "                              ON     EDV.ENUMNAME =  CDS.CONTROLCHARTTYPE AND EDV.ENUMVALUE =   CDS.CHARTNAME                                "+
                    "                           LEFT JOIN SPC_CONTROLDATARESULT CDR                                                                               "+
                    "                              ON     CDR.CHARTID = CD.CHARTID                                                                                "+
                    "                                 AND CDR.CONTROLDATASEQ =  CD.CONTROLDATASEQ AND CDR.SITENAME = EDV.UDF1                                     "+
                    "                           LEFT JOIN SPC_ALARMDATA AR                                                                                        "+
                    "                              ON     CDR.CHARTID = AR.CHARTID                                                                                "+
                    "                                 AND CDR.CONTROLDATASEQ =  AR.CONTROLDATASEQ AND EDV.UDF1 = AR.CHARTNAME                                     "+
                    "                     WHERE 1 = 1                                                                                                             "+
                    "                     GROUP BY CD.CHARTID,CD.CONTROLDATASEQ,CD.DCDATAID,CDS.CHARTNAME,CD.Timekey,EDV_1.ChartType,                             "+
                    " 					NVL (CDR.RESULT, '-123456.789')                                                                                           "+
                    "                   )                                                                                                                         "+
                    "                   PIVOT (MAX (RESULT)                                                                                                       "+
                    "                     FOR CHARTNAME                                                                                                           "+
                    "                     IN  ('Xbar' AS XBAR,'S' AS S,'R' AS R,'U1' AS U1,'U2' AS U2,'Min' AS MIN,'Max' AS MAX                                   "+
                    "                          ,'I' AS I,'mR' AS MR,'NP' AS NP,'P' AS P,'C' AS C,'U' AS U))                                                       "+
                    "                   ORDER BY CONTROLDATASEQ                                                                                                   "+
                    "                 ) SPC_DERIVEDDATA                                                                                                           "+
                    //               "               --Raw Data                                                                                                                    "+
                    "              INNER JOIN SPC_CHARTDEF CTDF ON CTDF.CHARTID = SPC_DERIVEDDATA.CHARTID                                                         "+
                    "              INNER JOIN SPC_CONTROLCHART CNCT ON CNCT.CHARTID = SPC_DERIVEDDATA.CHARTID                                                     "+
                    "              INNER JOIN SPC_DCDATA DD ON DD.DCDATAID = SPC_DERIVEDDATA.DCDATAID                                                             "+
                    "              LEFT JOIN SPC_MARKINGDATA MD ON     MD.CHARTID = SPC_DERIVEDDATA.CHARTID AND                                                   "+
                    " 			 MD.CONTROLDATASEQ = SPC_DERIVEDDATA.CONTROLDATASEQ                                                                               "+
                    "              LEFT JOIN SPC_CONTROLDATARESULT CDR ON CDR.CHARTID = SPC_DERIVEDDATA.CHARTID AND                                               "+
                    " 			 CDR.CONTROLDATASEQ = SPC_DERIVEDDATA.CONTROLDATASEQ  AND CDR.SITETYPE = 'Raw'                                                    "+
                    "              LEFT JOIN SPC_DCPOREFERENCEDATA DR  ON DR.DCDATAID = SPC_DERIVEDDATA.DCDATAID                                                  "+
                    "               and  DR.procprocessoperationname=CTDF.procprocessoperationname                                                                "+
                    "                                                                                                                                             "+
                    "                                                                                                                                             "+
                    "         GROUP BY SPC_DERIVEDDATA.CHARTID,SPC_DERIVEDDATA.CONTROLDATASEQ,CNCT.CONTROLCHARTTYPE ,                                             "+
                    " 		CNCT.CHARTOWNER,SPC_DERIVEDDATA.ChartType,                                                                                            "+
                    "                  DD.DCDATAID,DD.MATERIALNAME,                                                                                               "+
                    "                  DD.PROCESSTIME,DD.EVENTTIME,MD.MARKINGTIME,SPC_DERIVEDDATA.Timekey,                                                        "+
                    "                  DD.PRODUCTSPECNAME,DD.ECCODE,DD.OWNERCODE, DD.QTap,DD.RTC,DD.RUNMODE,                                                      "+
                    "                  DD.PROCESSOPERATIONNAME,DD.MACHINENAME,DD.UNITNAME,DD.SUBUNITNAME,DD.RECIPENAME,DD.EventUser,                              "+
                    "                  CTDF.ALIASITEMNAME ,CTDF.DCType, CTDF.FactoryName, CTDF.SubFactoryName,                                                    "+
                    "                  SPC_DERIVEDDATA.XBAR,SPC_DERIVEDDATA.S,SPC_DERIVEDDATA.R,SPC_DERIVEDDATA.U1,SPC_DERIVEDDATA.U2,                            "+
                    " 				 SPC_DERIVEDDATA.MIN,                                                                                                         "+
                    " 				 SPC_DERIVEDDATA.MAX,                                                                                                         "+
                    "                  SPC_DERIVEDDATA.I,SPC_DERIVEDDATA.MR,SPC_DERIVEDDATA.NP,SPC_DERIVEDDATA.P,SPC_DERIVEDDATA.C,SPC_DERIVEDDATA.U              "+
                    "                  ,DR.ProcProcessTime                                                                                                        "+
                    "                  ,DR.PROCPROCESSOPERATIONNAME,DR.PROCRECIPENAME,DR.PROCMACHINENAME,DR.PROCUNITNAME,DR.PROCSUBUNITNAME                       "+
                    "         ORDER BY CONTROLDATASEQ                                                                                                             "+
                    "     )  SPC_ALLDATA                                                                                                                          "+
                    "   ) CHOT_SPC_DATA_ALL                                                                                                                       "+
                    "                                                                                                                                             "+
                    "   Left JOIN (                                                                                                                               "+
                    "     /* Formatted on 2018/6/26 14:39 (By Peace) */                                                                                           "+
                    " select CHARTID,CONTROLDATASEQ,                                                                                                              "+
                    "    rtrim(regexp_substr(USPEC || ',', '.*?' || ',', 1, 1), ',') AS USPEC,                                                                    "+
                    "    rtrim(regexp_substr(TARGET || ',', '.*?' || ',', 1, 1), ',') AS TARGET,                                                                  "+
                    "    rtrim(regexp_substr(LSPEC || ',', '.*?' || ',', 1, 1), ',') AS LSPEC,                                                                    "+
                    "    rtrim(regexp_substr(UCL || ',', '.*?' || ',', 1, 1), ',') AS UCL1,                                                                       "+
                    "    rtrim(regexp_substr(CL || ',', '.*?' || ',', 1, 1), ',') AS CL1,                                                                         "+
                    "    rtrim(regexp_substr(LCL || ',', '.*?' || ',', 1, 1), ',') AS LCL1,                                                                       "+
                    "                                                                                                                                             "+
                    "    rtrim(regexp_substr(UCL || ',', '.*?' || ',', 1, 2), ',') AS UCL2,                                                                       "+
                    "    rtrim(regexp_substr(CL || ',', '.*?' || ',', 1, 2), ',') AS CL2,                                                                         "+
                    "    rtrim(regexp_substr(LCL || ',', '.*?' || ',', 1, 2), ',') AS LCL2,                                                                       "+
                    "    rtrim(regexp_substr(UCL || ',', '.*?' || ',', 1, 3), ',') AS UCL3,                                                                       "+
                    "    rtrim(regexp_substr(CL || ',', '.*?' || ',', 1, 3), ',') AS CL3,                                                                         "+
                    "    rtrim(regexp_substr(LCL || ',', '.*?' || ',', 1, 3), ',') AS LCL3                                                                        "+
                    "    ,rtrim(regexp_substr(OOCALARMID || ',', '.*?' || ',', 1, 1), ',') AS OOC1,                                                               "+
                    "    rtrim(regexp_substr(OOCALARMID || ',', '.*?' || ',', 1, 2), ',') AS OOC2,                                                                "+
                    "    rtrim(regexp_substr(OOCALARMID || ',', '.*?' || ',', 1, 3), ',') AS OOC3,                                                                "+
                    "    rtrim(regexp_substr(OOR_RULENAME || ',', '.*?' || ',', 1, 1), ',') AS OOR1,                                                              "+
                    "    rtrim(regexp_substr(OOR_RULENAME || ',', '.*?' || ',', 1, 2), ',') AS OOR2,                                                              "+
                    "    rtrim(regexp_substr(OOR_RULENAME || ',', '.*?' || ',', 1, 3), ',') AS OOR3                                                               "+
                    "  from  (                                                                                                                                    "+
                    "    SELECT   CD.CHARTID,CD.CONTROLDATASEQ                                                                                                    "+
                    "             ,LISTAGG (CDS.UPPERSPECLIMIT, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS USPEC                                                   "+
                    "             ,LISTAGG (CDS.TARGET, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS TARGET                                                          "+
                    "             ,LISTAGG (CDS.LOWERSPECLIMIT, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS LSPEC                                                   "+
                    "             ,LISTAGG (CDS.CENTERLINE, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS CL                                                          "+
                    "             ,LISTAGG (CDS.UPPERCONTROLLIMIT, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS UCL                                                  "+
                    "             ,LISTAGG (CDS.LOWERCONTROLLIMIT, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS LCL                                                  "+
                    "             ,LISTAGG (nvl(ADC.ALARMSEQ,'0'), ',') WITHIN GROUP (ORDER BY EDV.SEQ) AS OOCALARMID                                             "+
                    "             ,LISTAGG (nvl(ARDR.RULENAME,'0'), ',') WITHIN GROUP (ORDER BY EDV.SEQ) AS OOR_RULENAME                                          "+
                    "                                                                                                                                             "+
                    "     FROM (SELECT /*+ INDEX(CD SPC_CONTROLDATA_PK) */                                                                                        "+
                    "                  ROWNUM AS DATASEQ,                                                                                                         "+
                    "                   CD.CHARTID,                                                                                                               "+
                    "                   CD.CONTROLDATASEQ,                                                                                                        "+
                    "                   CD.DCDATAID,CD.Timekey                                                                                                    "+
                    "              FROM SPC_CONTROLDATA CD        WHERE                                                                                                 "+
//                    "                 CD.chartid='495A3ANK1_A5870_WETX05_CD1_LOSS'                                                                          "+
                    //                   "                    AND CD.ControlDataSeq >= '201806231400' AND CD.ControlDataSeq < '201806231600'                                           "+
                    "  CD.chartid='"+chartId+"' "+
                    "   and CD.ControlDataSeq >= '"+sdf.format(etlConfD.getRunStartTimestamp())+"'" +
                    "   AND  CD.ControlDataSeq< '"+sdf.format(etlConfD.getRunEndTimestamp())+"' "+
                    "           ) CD                                                                                                                              "+
                    "            LEFT JOIN SPC_CONTROLDATASPEC CDS                                                                                                "+
                    "               ON     CDS.CHARTID = CD.CHARTID                                                                                               "+
                    "                  AND CDS.CONTROLDATASEQ = CD.CONTROLDATASEQ                                                                                 "+
                    "            INNER JOIN SPC_ENUMDEFVALUE EDV                                                                                                  "+
                    "               ON EDV.ENUMNAME =   CDS.CONTROLCHARTTYPE  AND EDV.ENUMVALUE = CDS.CHARTNAME  and ENUMVALUE <>'Raw'                            "+
                    "            LEFT JOIN SPC_ALARMDATA ADC                                                                                                      "+
                    "               ON     ADC.CHARTID = CD.CHARTID                                                                                               "+
                    "                  AND ADC.CONTROLDATASEQ = CD.CONTROLDATASEQ                                                                                 "+
                    "                  AND ADC.CHARTNAME = EDV.ENUMVALUE and ADC.CHARTNAME <>'Raw'                                                                "+
                    "                  AND ADC.ALARMTYPE = 'OOC'                                                                                                  "+
                    "                  AND 1 =                                                                                                                    "+
                    "                         CASE                                                                                                                "+
                    "                            WHEN EDV.ENUMVALUE in ('Xbar', 'Raw')                                                                            "+
                    "                            THEN                                                                                                             "+
                    "                               CASE                                                                                                          "+
                    "                                  WHEN ADC.CHARTNAME IN ('Xbar', 'Raw') THEN 1                                                               "+
                    "                                  ELSE 0                                                                                                     "+
                    "                               END                                                                                                           "+
                    "                            ELSE                                                                                                             "+
                    "                               CASE                                                                                                          "+
                    "                                  WHEN EDV.ENUMVALUE = ADC.CHARTNAME THEN 1                                                                  "+
                    "                                  ELSE 0                                                                                                     "+
                    "                               END                                                                                                           "+
                    "                         END                                                                                                                 "+
                    "                                                                                                                                             "+
                    "            LEFT JOIN SPC_ALARMDATA ADR                                                                                                      "+
                    "               ON     ADR.CHARTID = CD.CHARTID                                                                                               "+
                    "                  AND ADR.CONTROLDATASEQ = CD.CONTROLDATASEQ                                                                                 "+
                    "                  AND ADR.CHARTNAME = EDV.ENUMVALUE                                                                                          "+
                    "                  AND ADR.ALARMTYPE = 'OOR'                                                                                                  "+
                    "            LEFT JOIN SPC_ALARMRULEOUTDATA ARDR                                                                                              "+
                    "               ON     ARDR.alarmseq = ADR.alarmseq                                                                                           "+
                    "                                                                                                                                             "+
                    "      WHERE 1 = 1                                                                                                                            "+
                    "   GROUP BY  CD.CHARTID,CD.CONTROLDATASEQ                                                                                                    "+
                    " ) SPC_SPEC_ALARM                                                                                                                            "+
                    "                                                                                                                                             "+
                    "   ) CHOT_SPC_SPEC_ALARM                                                                                                                     "+
                    "   ON  CHOT_SPC_SPEC_ALARM.CHARTID=CHOT_SPC_DATA_ALL.CHARTID and CHOT_SPC_SPEC_ALARM.CONTROLDATASEQ=CHOT_SPC_DATA_ALL.CONTROLDATASEQ         "+
                    "   Left join (                                                                                                                               "+
                    "   select CONTROLDATASEQ,         " +
                    "         LISTAGG ( ADS_ALARMSEQ, ',') WITHIN GROUP (ORDER BY 1) AS OOSALARMID," +
                    "         LISTAGG ( ADCRAW_ALARMSEQ, ',') WITHIN GROUP (ORDER BY 1) AS OOCR_ALARMID " +
                    " from (         " +
                    "   SELECT " +
                    "            CD.CONTROLDATASEQ,   " +
                    "            max(nvl(ADS.ALARMSEQ,'0')) as ADS_ALARMSEQ," +
                    "            max(nvl(ADCRAW.ALARMSEQ,'0')) as ADCRAW_ALARMSEQ " +
                    "   FROM (SELECT /*+ INDEX(CD SPC_CONTROLDATA_PK) */" +
                    "               CD.CHARTID," +
                    "               CD.CONTROLDATASEQ," +
                    "               CD.DCDATAID,CD.Timekey" +
                    "          FROM SPC_CONTROLDATA CD" +
                    "          WHERE                                                                                                                                           "+
//                    "           CD.chartid='495A3ANK1_A5870_WETX05_CD1_LOSS'                                                                               "+
//                    "              AND CD.ControlDataSeq >= '201806231400' AND CD.ControlDataSeq < '201806231600'                                                 "+
                    "  CD.chartid='"+chartId+"' "+
                    "   and CD.ControlDataSeq >= '"+sdf.format(etlConfD.getRunStartTimestamp())+"'" +
                    "   AND  CD.ControlDataSeq< '"+sdf.format(etlConfD.getRunEndTimestamp())+"' "+
                    "     ) CD " +
                    "        LEFT JOIN SPC_CONTROLDATASPEC CDS" +
                    "           ON     CDS.CHARTID = CD.CHARTID" +
                    "              AND CDS.CONTROLDATASEQ = CD.CONTROLDATASEQ" +
                    "        INNER JOIN SPC_ENUMDEFVALUE EDV" +
                    "           ON EDV.ENUMNAME =   CDS.CONTROLCHARTTYPE  AND EDV.ENUMVALUE = CDS.CHARTNAME  and EDV.ENUMVALUE ='Raw'" +
                    "        LEFT JOIN SPC_ALARMDATA ADS" +
                    "           ON     ADS.CHARTID = CD.CHARTID" +
                    "              AND ADS.CONTROLDATASEQ = CD.CONTROLDATASEQ AND ADS.ALARMTYPE = 'OOS'" +
                    "        LEFT JOIN SPC_ALARMDATA ADCRAW" +
                    "           ON     ADCRAW.CHARTID = CD.CHARTID" +
                    "              AND ADCRAW.CONTROLDATASEQ = CD.CONTROLDATASEQ AND ADCRAW.CHARTNAME = EDV.ENUMVALUE   AND ADCRAW.ALARMTYPE = 'OOC'               " +
                    "   WHERE 1 = 1 " +
                    "   GROUP BY CD.CONTROLDATASEQ " +
                    ") OOSC" +
                    " GROUP BY CONTROLDATASEQ                                          "+
                    "   ) CHOT_SPC_OOS_OOCR                                                                                                                       "+
                    "   ON  CHOT_SPC_OOS_OOCR.CONTROLDATASEQ=CHOT_SPC_DATA_ALL.CONTROLDATASEQ                                                                     "+
                    " where 1=1                                                                                                                                   "+
                    " order BY  CHOT_SPC_DATA_ALL.CHARTID,CHOT_SPC_DATA_ALL.CONTROLDATASEQ                                                                        ";
            sqlbuff.append(" INSERT INTO " + tableName + " (" + CONTROLDATA_QUERY_COLUMN + ") " + sql);

            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuff.toString());
            count = queryLoadStep.executeUpdate();
        }catch (Exception e){
            logger.error("{} saveChartDataByChartId error,chartId:{},tableName:{},start:{},end:{},error {},",etlConfD.getJobName(),chartId,tableName,sdf.format(etlConfD.getRunStartTimestamp()),sdf.format(etlConfD.getRunEndTimestamp()) ,e.getMessage());
            SpcEtlErrorData spcEtlErrorData =new SpcEtlErrorData();
            spcEtlErrorData.setChartId(chartId);
            spcEtlErrorData.setTabName(tableName);
            spcEtlErrorData.setStartSeq(sdf.format(etlConfD.getRunStartTimestamp()));
            spcEtlErrorData.setEndSeq(sdf.format(etlConfD.getRunEndTimestamp()));
            spcEtlErrorData.setErrorMessage(e.getMessage());
            spcEtlErrList.add(spcEtlErrorData);
            //     dao.save(spcEtlErrorData);

            //      throw new ETLException(e.getMessage()+" chartid: "+chartId);
        }
        return count;
    }

    public void updateSpcControlChart(EtlConfD etlConfD,Session session )throws Exception{
        int count =0;
        List<ChartType> list =new ArrayList<>();
        String shopId =etlConfD.getShop();
        int type   = Integer.parseInt(etlConfD.getExt1());
        String eqpt   =etlConfD.getEqpt();
        String dcType =etlConfD.getCellType();
        String sql="";
        try{
            sql=" update SPC_CONTROLCHART set  Last_data_Seq " +
                    "=  '"+sdf.format(etlConfD.getRunStartTimestamp())+"'        where chartid in (" +
                    " SELECT distinct CD.ChartID                       "+
                    "       FROM (SELECT /*+ INDEX(CD SPC_CONTROLDATA_IDX1) */  "+
                    "                    DISTINCT CD.ChartID                    "+
                    "               FROM spc_controldata CD    WHERE   1=1         "+
//                     "and CD.chartid='495A3ANK1_A5870_WETX05_CD1_LOSS'"+
                    //                   "         AND   CD.TIMEKEY >= '201806231400'                    "+
                    //                   "                    AND CD.TIMEKEY < '201806231630'        "+
                    " AND CD.TIMEKEY >= '"+sdf.format(etlConfD.getRunStartTimestamp())+"'" +
                    " AND CD.TIMEKEY < '"+sdf.format(etlConfD.getRunEndTimestamp()) +"' "+
                    "            ) CD,                                          "+
                    "            spc_chartdef CTF,                              "+
                    "            spc_CONTROLCHART CC,                           "+
                    "            SPC_ENUMDEFVALUE EV   WHERE                         "+
                    "           CTF.FactoryName = '"+shopId+"'                    "+
                    "            AND CTF.DCType = '"+dcType+"'                  "+
//                     "           CTF.FactoryName = 'ACF'                    "+
                    //                    "            AND CTF.DCType = 'Inspection'                  "+
                    "            AND CTF.chartid = CD.chartid                   "+
                    "            AND CC.ChartID = CD.ChartID                    "+
                    "            AND EV.ACTIVEFLAG = 'Y'                        "+
                    "            AND EV.enumname = 'ControlChartType'           "+
                    "            AND EV.ENUMVALUE = CC.CONTROLCHARTTYPE         ";
            if(StringUtils.isNotBlank(eqpt) && type==0){
                sql+=" AND CTF.Machinename in( "+eqpt+") ";
            }
            if(StringUtils.isNotBlank(eqpt) && type==1){
                sql+=" AND  regexp_like(CTF.Machinename ,'^"+eqpt+"') ";
            }
            sql+=    "                    )    ";
            long startTime=System.currentTimeMillis();
            SQLQuery queryLoadStep = session.createSQLQuery(sql);
            count = queryLoadStep.executeUpdate();
            long endTime=System.currentTimeMillis();
            logger.info("{} update SPC_CONTROLCHART 成功，count：{},{}ms",etlConfD.getJobName(),count,endTime-startTime);

        }catch (Exception e){
            logger.error("{} update SPC_CONTROLCHART error :{}----shopId:{}---dcType:{}---start:{}---end:{}---sql:{}",etlConfD.getJobName(),e.getMessage(),shopId,dcType,sdf.format(etlConfD.getRunStartTimestamp()),sdf.format(etlConfD.getRunEndTimestamp()),sql);
            throw new Exception("update SPC_CONTROLCHART Exception:"+ e.getMessage());
        }


    }
    public void saveSpcEtlErrorData(Collection<SpcEtlErrorData> spcEtlErrList)throws Exception{
        try{
            dao.update(spcEtlErrList);
            //  dao.save(spcEtlErrList.get(0));
            //     dao.saveAll(spcEtlErrList);
            logger.info("saveSpcEtlErrorData success,count {} ",spcEtlErrList.size());
        }catch (Exception e){
            logger.error("saveSpcEtlErrorData error {}",e);
            throw new Exception("saveSpcEtlErrorData Exception:"+ e.getMessage());
        }
    }
    public Map<String,String> reSaveChartDataByChartId(SpcEtlErrorData spcEtlErrData,Session session ) throws Exception{
        int count =0;
        Map<String,String> map=new HashMap<String,String>(3);
        StringBuffer sqlbuff = new StringBuffer();
        try {
            String sql  =" select CHOT_SPC_DATA_ALL.CHARTID, CONTROLCHARTTYPE ,CHARTOWNER, FactoryName, SubFactoryName,       "+
                    "        CHOT_SPC_DATA_ALL.CONTROLDATASEQ,DCDATAID, Timekey,DCType,ChartType,                             "+
                    "        MATERIALNAME,ALIASITEMNAME ,PRODUCTSPECNAME,ECCODE,RUNMODE,OWNERCODE,RTC,QTap,EventUser,         "+
                    "        to_date(EVENTTIME,'YYYY-MM-DD HH24:MI:SS'),PROCESSOPERATIONNAME,MACHINENAME,UNITNAME,RECIPENAME, "+
                    "        ProcProcessTime,PROCPROCESSOPERATIONNAME,PROCMACHINENAME,PROCUNITNAME,PROCRECIPENAME,            "+
                    "        USPEC,TARGET,LSPEC,UCL1,CL1,LCL1,UCL2,CL2,LCL2,UCL3,CL3,LCL3,                                    "+
                    "        XBAR,S,R,U1,U2,MAX,MIN,I,MR,NP,P,C,U,MARKINGFLAG,                                                "+
                    "        case when OOSALARMID>0 then 'Y' else 'N' end as OOS,                                             "+
                    "        case when OOCR_ALARMID> 0 or OOC1>0 then 'Y'  else 'N'  end as OOC1,                             "+
                    "        case when OOC2>0 then 'Y' else 'N' end as OOC2,                                                  "+
                    "        case when OOC3>0 then 'Y' else 'N' end as OOC3,                                                  "+
                    "        OOR1,OOR2,OOR3,                                                                                  "+
                    "        RawDataQty,  RAWDATA                                                                             "+
                    " from                                                                                                                                        "+
                    "    (                                                                                                                                        "+
                    "     SELECT SPC_ALLDATA.*, case when SPC_ALLDATA.rawdata is  null then 0 else REGEXP_COUNT(SPC_ALLDATA.rawdata,',')+1 end as RawDataQty      "+
                    "     FROM (                                                                                                                                  "+
                    "         SELECT SPC_DERIVEDDATA.CHARTID,SPC_DERIVEDDATA.CONTROLDATASEQ,CNCT.CONTROLCHARTTYPE , CNCT.CHARTOWNER,SPC_DERIVEDDATA.ChartType,    "+
                    "               DD.DCDATAID,DD.MATERIALNAME,                                                                                                  "+
                    "               TO_CHAR (DD.PROCESSTIME, 'YYYY-MM-DD HH24:MI:SS')  AS PROCESSTIME,SPC_DERIVEDDATA.Timekey,                                    "+
                    "               TO_CHAR (DD.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS')  AS EVENTTIME,                                                                "+
                    "               CASE  WHEN MD.MARKINGTIME IS NOT NULL THEN 'Y'  ELSE 'N' END AS MARKINGFLAG,                                                  "+
                    "               DD.PRODUCTSPECNAME,DD.ECCODE,DD.OWNERCODE, DD.QTap,DD.RTC,DD.RUNMODE,                                                         "+
                    "               DD.PROCESSOPERATIONNAME,DD.MACHINENAME,DD.UNITNAME,DD.SUBUNITNAME,DD.RECIPENAME,DD.EventUser,                                 "+
                    "               CTDF.ALIASITEMNAME ,CTDF.DCType, CTDF.FactoryName, CTDF.SubFactoryName,                                                       "+
                    "               SPC_DERIVEDDATA.XBAR,SPC_DERIVEDDATA.S,SPC_DERIVEDDATA.R,SPC_DERIVEDDATA.U1,SPC_DERIVEDDATA.U2,SPC_DERIVEDDATA.MIN,           "+
                    " 			  SPC_DERIVEDDATA.MAX,                                                                                                            "+
                    "               SPC_DERIVEDDATA.I,SPC_DERIVEDDATA.MR,SPC_DERIVEDDATA.NP,SPC_DERIVEDDATA.P,SPC_DERIVEDDATA.C,SPC_DERIVEDDATA.U                 "+
                    "               ,DR.ProcProcessTime                                                                                                           "+
                    "               ,DR.PROCPROCESSOPERATIONNAME,DR.PROCRECIPENAME,DR.PROCMACHINENAME,DR.PROCUNITNAME,DR.PROCSUBUNITNAME                          "+
                    "               ,MAX (SPC_DERIVEDDATA.ALARMTYPE) AS ALARMTYPE                                                                                 "+
                    "               ,  LISTAGG (CDR.RESULT, ',')  WITHIN GROUP (ORDER BY CDR.SITENAME ) AS RAWDATA                                                "+
                    "         FROM (                                                                                                                              "+
                    "             SELECT *                                                                                                                        "+
                    "             FROM (  SELECT CD.CHARTID,  CD.CONTROLDATASEQ, CD.DCDATAID,CD.Timekey,   CDS.CHARTNAME,EDV_1.ChartType,                         "+
                    "                            NVL (CDR.RESULT, '-123456.789') AS RESULT,                                                                       "+
                    "                            MAX (  MAX (AR.ALARMTYPE)) OVER (  PARTITION BY CD.CHARTID, CD.CONTROLDATASEQ, CD.DCDATAID)  AS ALARMTYPE        "+
                    "                     FROM (SELECT /*+ INDEX(CD SPC_CONTROLDATA_PK) */                                                                        "+
                    "                                  CD.CHARTID,  CD.CONTROLDATASEQ,  CD.DCDATAID,CD.Timekey                                                    "+
                    "                             FROM SPC_CONTROLDATA CD   WHERE                                                                                       "+
//                   "                                CD.chartid='495A3ANK1_A5870_WETX05_CD1_LOSS'                                                           "+
//                    "                                   AND CD.ControlDataSeq >= '201806231400' AND CD.ControlDataSeq < '201806231600'                            "+
                    "  CD.chartid='"+spcEtlErrData.getChartId()+"' "+
                    "   and CD.ControlDataSeq >= '"+spcEtlErrData.getStartSeq()+"'" +
                    "   AND  CD.ControlDataSeq< '"+spcEtlErrData.getEndSeq()+"' "+
                    "                           ) CD                                                                                                              "+
                    "                           INNER JOIN SPC_CONTROLDATASPEC CDS                                                                                "+
                    "                              ON     CDS.CHARTID = CD.CHARTID AND CDS.CONTROLDATASEQ =  CD.CONTROLDATASEQ                                    "+
                    "                           INNER JOIN (select ENUMVALUE, (UDF1 || ',' || UDF2 || ',' || UDF3) as ChartType  from SPC_ENUMDEFVALUE            "+
                    " 						  where ENUMNAME =  'ControlChartType' and UDF1 is not NULL )  EDV_1                                                  "+
                    "                              ON     EDV_1.ENUMVALUE =   CDS.CONTROLCHARTTYPE                                                                "+
                    "                           INNER JOIN SPC_ENUMDEFVALUE EDV                                                                                   "+
                    "                              ON     EDV.ENUMNAME =  CDS.CONTROLCHARTTYPE AND EDV.ENUMVALUE =   CDS.CHARTNAME                                "+
                    "                           LEFT JOIN SPC_CONTROLDATARESULT CDR                                                                               "+
                    "                              ON     CDR.CHARTID = CD.CHARTID                                                                                "+
                    "                                 AND CDR.CONTROLDATASEQ =  CD.CONTROLDATASEQ AND CDR.SITENAME = EDV.UDF1                                     "+
                    "                           LEFT JOIN SPC_ALARMDATA AR                                                                                        "+
                    "                              ON     CDR.CHARTID = AR.CHARTID                                                                                "+
                    "                                 AND CDR.CONTROLDATASEQ =  AR.CONTROLDATASEQ AND EDV.UDF1 = AR.CHARTNAME                                     "+
                    "                     WHERE 1 = 1                                                                                                             "+
                    "                     GROUP BY CD.CHARTID,CD.CONTROLDATASEQ,CD.DCDATAID,CDS.CHARTNAME,CD.Timekey,EDV_1.ChartType,                             "+
                    " 					NVL (CDR.RESULT, '-123456.789')                                                                                           "+
                    "                   )                                                                                                                         "+
                    "                   PIVOT (MAX (RESULT)                                                                                                       "+
                    "                     FOR CHARTNAME                                                                                                           "+
                    "                     IN  ('Xbar' AS XBAR,'S' AS S,'R' AS R,'U1' AS U1,'U2' AS U2,'Min' AS MIN,'Max' AS MAX                                   "+
                    "                          ,'I' AS I,'mR' AS MR,'NP' AS NP,'P' AS P,'C' AS C,'U' AS U))                                                       "+
                    "                   ORDER BY CONTROLDATASEQ                                                                                                   "+
                    "                 ) SPC_DERIVEDDATA                                                                                                           "+
                    //               "               --Raw Data                                                                                                                    "+
                    "              INNER JOIN SPC_CHARTDEF CTDF ON CTDF.CHARTID = SPC_DERIVEDDATA.CHARTID                                                         "+
                    "              INNER JOIN SPC_CONTROLCHART CNCT ON CNCT.CHARTID = SPC_DERIVEDDATA.CHARTID                                                     "+
                    "              INNER JOIN SPC_DCDATA DD ON DD.DCDATAID = SPC_DERIVEDDATA.DCDATAID                                                             "+
                    "              LEFT JOIN SPC_MARKINGDATA MD ON     MD.CHARTID = SPC_DERIVEDDATA.CHARTID AND                                                   "+
                    " 			 MD.CONTROLDATASEQ = SPC_DERIVEDDATA.CONTROLDATASEQ                                                                               "+
                    "              LEFT JOIN SPC_CONTROLDATARESULT CDR ON CDR.CHARTID = SPC_DERIVEDDATA.CHARTID AND                                               "+
                    " 			 CDR.CONTROLDATASEQ = SPC_DERIVEDDATA.CONTROLDATASEQ  AND CDR.SITETYPE = 'Raw'                                                    "+
                    "              LEFT JOIN SPC_DCPOREFERENCEDATA DR  ON DR.DCDATAID = SPC_DERIVEDDATA.DCDATAID                                                  "+
                    "               and  DR.procprocessoperationname=CTDF.procprocessoperationname                                                                "+
                    "                                                                                                                                             "+
                    "                                                                                                                                             "+
                    "         GROUP BY SPC_DERIVEDDATA.CHARTID,SPC_DERIVEDDATA.CONTROLDATASEQ,CNCT.CONTROLCHARTTYPE ,                                             "+
                    " 		CNCT.CHARTOWNER,SPC_DERIVEDDATA.ChartType,                                                                                            "+
                    "                  DD.DCDATAID,DD.MATERIALNAME,                                                                                               "+
                    "                  DD.PROCESSTIME,DD.EVENTTIME,MD.MARKINGTIME,SPC_DERIVEDDATA.Timekey,                                                        "+
                    "                  DD.PRODUCTSPECNAME,DD.ECCODE,DD.OWNERCODE, DD.QTap,DD.RTC,DD.RUNMODE,                                                      "+
                    "                  DD.PROCESSOPERATIONNAME,DD.MACHINENAME,DD.UNITNAME,DD.SUBUNITNAME,DD.RECIPENAME,DD.EventUser,                              "+
                    "                  CTDF.ALIASITEMNAME ,CTDF.DCType, CTDF.FactoryName, CTDF.SubFactoryName,                                                    "+
                    "                  SPC_DERIVEDDATA.XBAR,SPC_DERIVEDDATA.S,SPC_DERIVEDDATA.R,SPC_DERIVEDDATA.U1,SPC_DERIVEDDATA.U2,                            "+
                    " 				 SPC_DERIVEDDATA.MIN,                                                                                                         "+
                    " 				 SPC_DERIVEDDATA.MAX,                                                                                                         "+
                    "                  SPC_DERIVEDDATA.I,SPC_DERIVEDDATA.MR,SPC_DERIVEDDATA.NP,SPC_DERIVEDDATA.P,SPC_DERIVEDDATA.C,SPC_DERIVEDDATA.U              "+
                    "                  ,DR.ProcProcessTime                                                                                                        "+
                    "                  ,DR.PROCPROCESSOPERATIONNAME,DR.PROCRECIPENAME,DR.PROCMACHINENAME,DR.PROCUNITNAME,DR.PROCSUBUNITNAME                       "+
                    "         ORDER BY CONTROLDATASEQ                                                                                                             "+
                    "     )  SPC_ALLDATA                                                                                                                          "+
                    "   ) CHOT_SPC_DATA_ALL                                                                                                                       "+
                    "                                                                                                                                             "+
                    "   Left JOIN (                                                                                                                               "+
                    "     /* Formatted on 2018/6/26 14:39 (By Peace) */                                                                                           "+
                    " select CHARTID,CONTROLDATASEQ,                                                                                                              "+
                    "    rtrim(regexp_substr(USPEC || ',', '.*?' || ',', 1, 1), ',') AS USPEC,                                                                    "+
                    "    rtrim(regexp_substr(TARGET || ',', '.*?' || ',', 1, 1), ',') AS TARGET,                                                                  "+
                    "    rtrim(regexp_substr(LSPEC || ',', '.*?' || ',', 1, 1), ',') AS LSPEC,                                                                    "+
                    "    rtrim(regexp_substr(UCL || ',', '.*?' || ',', 1, 1), ',') AS UCL1,                                                                       "+
                    "    rtrim(regexp_substr(CL || ',', '.*?' || ',', 1, 1), ',') AS CL1,                                                                         "+
                    "    rtrim(regexp_substr(LCL || ',', '.*?' || ',', 1, 1), ',') AS LCL1,                                                                       "+
                    "                                                                                                                                             "+
                    "    rtrim(regexp_substr(UCL || ',', '.*?' || ',', 1, 2), ',') AS UCL2,                                                                       "+
                    "    rtrim(regexp_substr(CL || ',', '.*?' || ',', 1, 2), ',') AS CL2,                                                                         "+
                    "    rtrim(regexp_substr(LCL || ',', '.*?' || ',', 1, 2), ',') AS LCL2,                                                                       "+
                    "    rtrim(regexp_substr(UCL || ',', '.*?' || ',', 1, 3), ',') AS UCL3,                                                                       "+
                    "    rtrim(regexp_substr(CL || ',', '.*?' || ',', 1, 3), ',') AS CL3,                                                                         "+
                    "    rtrim(regexp_substr(LCL || ',', '.*?' || ',', 1, 3), ',') AS LCL3                                                                        "+
                    "    ,rtrim(regexp_substr(OOCALARMID || ',', '.*?' || ',', 1, 1), ',') AS OOC1,                                                               "+
                    "    rtrim(regexp_substr(OOCALARMID || ',', '.*?' || ',', 1, 2), ',') AS OOC2,                                                                "+
                    "    rtrim(regexp_substr(OOCALARMID || ',', '.*?' || ',', 1, 3), ',') AS OOC3,                                                                "+
                    "    rtrim(regexp_substr(OOR_RULENAME || ',', '.*?' || ',', 1, 1), ',') AS OOR1,                                                              "+
                    "    rtrim(regexp_substr(OOR_RULENAME || ',', '.*?' || ',', 1, 2), ',') AS OOR2,                                                              "+
                    "    rtrim(regexp_substr(OOR_RULENAME || ',', '.*?' || ',', 1, 3), ',') AS OOR3                                                               "+
                    "  from  (                                                                                                                                    "+
                    "    SELECT   CD.CHARTID,CD.CONTROLDATASEQ                                                                                                    "+
                    "             ,LISTAGG (CDS.UPPERSPECLIMIT, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS USPEC                                                   "+
                    "             ,LISTAGG (CDS.TARGET, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS TARGET                                                          "+
                    "             ,LISTAGG (CDS.LOWERSPECLIMIT, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS LSPEC                                                   "+
                    "             ,LISTAGG (CDS.CENTERLINE, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS CL                                                          "+
                    "             ,LISTAGG (CDS.UPPERCONTROLLIMIT, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS UCL                                                  "+
                    "             ,LISTAGG (CDS.LOWERCONTROLLIMIT, ',')  WITHIN GROUP (ORDER BY EDV.SEQ ) AS LCL                                                  "+
                    "             ,LISTAGG (nvl(ADC.ALARMSEQ,'0'), ',') WITHIN GROUP (ORDER BY EDV.SEQ) AS OOCALARMID                                             "+
                    "             ,LISTAGG (nvl(ARDR.RULENAME,'0'), ',') WITHIN GROUP (ORDER BY EDV.SEQ) AS OOR_RULENAME                                          "+
                    "                                                                                                                                             "+
                    "     FROM (SELECT /*+ INDEX(CD SPC_CONTROLDATA_PK) */                                                                                        "+
                    "                  ROWNUM AS DATASEQ,                                                                                                         "+
                    "                   CD.CHARTID,                                                                                                               "+
                    "                   CD.CONTROLDATASEQ,                                                                                                        "+
                    "                   CD.DCDATAID,CD.Timekey                                                                                                    "+
                    "              FROM SPC_CONTROLDATA CD        WHERE                                                                                                 "+
//                    "                 CD.chartid='495A3ANK1_A5870_WETX05_CD1_LOSS'                                                                          "+
                    //                   "                    AND CD.ControlDataSeq >= '201806231400' AND CD.ControlDataSeq < '201806231600'                                           "+
                    "  CD.chartid='"+spcEtlErrData.getChartId()+"' "+
                    "   and CD.ControlDataSeq >= '"+spcEtlErrData.getStartSeq()+"'" +
                    "   AND  CD.ControlDataSeq< '"+spcEtlErrData.getEndSeq()+"' "+
                    "           ) CD                                                                                                                              "+
                    "            LEFT JOIN SPC_CONTROLDATASPEC CDS                                                                                                "+
                    "               ON     CDS.CHARTID = CD.CHARTID                                                                                               "+
                    "                  AND CDS.CONTROLDATASEQ = CD.CONTROLDATASEQ                                                                                 "+
                    "            INNER JOIN SPC_ENUMDEFVALUE EDV                                                                                                  "+
                    "               ON EDV.ENUMNAME =   CDS.CONTROLCHARTTYPE  AND EDV.ENUMVALUE = CDS.CHARTNAME  and ENUMVALUE <>'Raw'                            "+
                    "            LEFT JOIN SPC_ALARMDATA ADC                                                                                                      "+
                    "               ON     ADC.CHARTID = CD.CHARTID                                                                                               "+
                    "                  AND ADC.CONTROLDATASEQ = CD.CONTROLDATASEQ                                                                                 "+
                    "                  AND ADC.CHARTNAME = EDV.ENUMVALUE and ADC.CHARTNAME <>'Raw'                                                                "+
                    "                  AND ADC.ALARMTYPE = 'OOC'                                                                                                  "+
                    "                  AND 1 =                                                                                                                    "+
                    "                         CASE                                                                                                                "+
                    "                            WHEN EDV.ENUMVALUE in ('Xbar', 'Raw')                                                                            "+
                    "                            THEN                                                                                                             "+
                    "                               CASE                                                                                                          "+
                    "                                  WHEN ADC.CHARTNAME IN ('Xbar', 'Raw') THEN 1                                                               "+
                    "                                  ELSE 0                                                                                                     "+
                    "                               END                                                                                                           "+
                    "                            ELSE                                                                                                             "+
                    "                               CASE                                                                                                          "+
                    "                                  WHEN EDV.ENUMVALUE = ADC.CHARTNAME THEN 1                                                                  "+
                    "                                  ELSE 0                                                                                                     "+
                    "                               END                                                                                                           "+
                    "                         END                                                                                                                 "+
                    "                                                                                                                                             "+
                    "            LEFT JOIN SPC_ALARMDATA ADR                                                                                                      "+
                    "               ON     ADR.CHARTID = CD.CHARTID                                                                                               "+
                    "                  AND ADR.CONTROLDATASEQ = CD.CONTROLDATASEQ                                                                                 "+
                    "                  AND ADR.CHARTNAME = EDV.ENUMVALUE                                                                                          "+
                    "                  AND ADR.ALARMTYPE = 'OOR'                                                                                                  "+
                    "            LEFT JOIN SPC_ALARMRULEOUTDATA ARDR                                                                                              "+
                    "               ON     ARDR.alarmseq = ADR.alarmseq                                                                                           "+
                    "                                                                                                                                             "+
                    "      WHERE 1 = 1                                                                                                                            "+
                    "   GROUP BY  CD.CHARTID,CD.CONTROLDATASEQ                                                                                                    "+
                    " ) SPC_SPEC_ALARM                                                                                                                            "+
                    "                                                                                                                                             "+
                    "   ) CHOT_SPC_SPEC_ALARM                                                                                                                     "+
                    "   ON  CHOT_SPC_SPEC_ALARM.CHARTID=CHOT_SPC_DATA_ALL.CHARTID and CHOT_SPC_SPEC_ALARM.CONTROLDATASEQ=CHOT_SPC_DATA_ALL.CONTROLDATASEQ         "+
                    "   Left join (                                                                                                                               "+
                    "   select CONTROLDATASEQ,         " +
                    "         LISTAGG ( ADS_ALARMSEQ, ',') WITHIN GROUP (ORDER BY 1) AS OOSALARMID," +
                    "         LISTAGG ( ADCRAW_ALARMSEQ, ',') WITHIN GROUP (ORDER BY 1) AS OOCR_ALARMID " +
                    " from (         " +
                    "   SELECT " +
                    "            CD.CONTROLDATASEQ,   " +
                    "            max(nvl(ADS.ALARMSEQ,'0')) as ADS_ALARMSEQ," +
                    "            max(nvl(ADCRAW.ALARMSEQ,'0')) as ADCRAW_ALARMSEQ " +
                    "   FROM (SELECT /*+ INDEX(CD SPC_CONTROLDATA_PK) */" +
                    "               CD.CHARTID," +
                    "               CD.CONTROLDATASEQ," +
                    "               CD.DCDATAID,CD.Timekey" +
                    "          FROM SPC_CONTROLDATA CD" +
                    "          WHERE                                                                                                                                           "+
//                    "           CD.chartid='495A3ANK1_A5870_WETX05_CD1_LOSS'                                                                               "+
//                    "              AND CD.ControlDataSeq >= '201806231400' AND CD.ControlDataSeq < '201806231600'                                                 "+
                    "  CD.chartid='"+spcEtlErrData.getChartId()+"' "+
                    "   and CD.ControlDataSeq >= '"+spcEtlErrData.getStartSeq()+"'" +
                    "   AND  CD.ControlDataSeq< '"+spcEtlErrData.getEndSeq()+"' "+
                    "     ) CD " +
                    "        LEFT JOIN SPC_CONTROLDATASPEC CDS" +
                    "           ON     CDS.CHARTID = CD.CHARTID" +
                    "              AND CDS.CONTROLDATASEQ = CD.CONTROLDATASEQ" +
                    "        INNER JOIN SPC_ENUMDEFVALUE EDV" +
                    "           ON EDV.ENUMNAME =   CDS.CONTROLCHARTTYPE  AND EDV.ENUMVALUE = CDS.CHARTNAME  and EDV.ENUMVALUE ='Raw'" +
                    "        LEFT JOIN SPC_ALARMDATA ADS" +
                    "           ON     ADS.CHARTID = CD.CHARTID" +
                    "              AND ADS.CONTROLDATASEQ = CD.CONTROLDATASEQ AND ADS.ALARMTYPE = 'OOS'" +
                    "        LEFT JOIN SPC_ALARMDATA ADCRAW" +
                    "           ON     ADCRAW.CHARTID = CD.CHARTID" +
                    "              AND ADCRAW.CONTROLDATASEQ = CD.CONTROLDATASEQ AND ADCRAW.CHARTNAME = EDV.ENUMVALUE   AND ADCRAW.ALARMTYPE = 'OOC'               " +
                    "   WHERE 1 = 1 " +
                    "   GROUP BY CD.CONTROLDATASEQ " +
                    ") OOSC" +
                    " GROUP BY CONTROLDATASEQ                                          "+
                    "   ) CHOT_SPC_OOS_OOCR                                                                                                                       "+
                    "   ON  CHOT_SPC_OOS_OOCR.CONTROLDATASEQ=CHOT_SPC_DATA_ALL.CONTROLDATASEQ                                                                     "+
                    " where 1=1                                                                                                                                   "+
                    " order BY  CHOT_SPC_DATA_ALL.CHARTID,CHOT_SPC_DATA_ALL.CONTROLDATASEQ                                                                        ";
            sqlbuff.append(" INSERT INTO " + spcEtlErrData.getTabName() + " (" + CONTROLDATA_QUERY_COLUMN + ") " + sql);

            SQLQuery queryLoadStep = session.createSQLQuery(sqlbuff.toString());
            count = queryLoadStep.executeUpdate();
            map.put("count",""+count);
            map.put("flag","success");
            map.put("message","");
        }catch (Exception e){
            logger.error(" reSaveChartDataByChartId error,chartId:{},tableName:{},start:{},end:{},error {},",spcEtlErrData.getChartId(),spcEtlErrData.getTabName(),spcEtlErrData.getStartSeq(),spcEtlErrData.getEndSeq() ,e.getMessage());
            map.put("count","0");
            map.put("flag","error");
            map.put("message",e.getMessage());
            /*SpcEtlErrorData spcEtlErrorData =new SpcEtlErrorData();
            spcEtlErrorData.setChartId(chartId);
            spcEtlErrorData.setTabName(tableName);
            spcEtlErrorData.setStartSeq(sdf.format(etlConfD.getRunStartTimestamp()));
            spcEtlErrorData.setEndSeq(sdf.format(etlConfD.getRunEndTimestamp()));
            spcEtlErrorData.setErrorMessage(e.getMessage());
            spcEtlErrList.add(spcEtlErrorData);*/
            //     dao.save(spcEtlErrorData);

            //           throw new Exception(e.getMessage());
        }finally {
            return map;
        }

    }


}
